package com.cn.jdia.springmvc.handlers;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.context.support.ClassPathXmlApplicationContext;

//Data sync Thread
public class DataSyncThread extends Thread {

	public void run() {
		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		while (true) {

			DateTime datetime = new DateTime();
			String nowDate = datetime.toString("YYYYMMdd");

			String nowTime = datetime.toString("HHmmss");
			System.out.println("Run Data Sync ! --" + nowTime + "--");

			// 测试完成后再打开，锁定时间段
			if (((nowTime.compareTo("091000") > 0) && (nowTime.compareTo("160000") < 0))
					|| ((nowTime.compareTo("173000") > 0)) || ((nowTime.compareTo("070000") < 0))) {
				System.out.println("超出考勤时间,sleep...");
				try {
					Thread.sleep(180000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				continue;
			}

			String yesDateWithLine = datetime.minusDays(1).toString("YYYY-MM-dd");
			String timestap = "";
			String yestap = "";

			timestap = datetime.minusSeconds(15).toString("YYYY-MM-dd HH:mm:ss");
			yestap = datetime.minusDays(1).toString("YYYYMMdd");

			try {
				List<Map<String, String>> list = new ArrayList<Map<String, String>>();

				Connection conn = ds.getConnection();
				Statement st = conn.createStatement();

				// 检测 1 & policy
				// if ((nowTime.compareTo("082400") > 0) &&
				// (nowTime.compareTo("082405") < 0)) {
				// StringBuffer psb = new StringBuffer("select * from
				// jan_attence where check_date='").append(nowDate)
				// .append("' and person_dept='公安分局'");
				//
				// ResultSet prs = st.executeQuery(psb.toString());
				// boolean yesP = false;
				// boolean todP = false;
				// while (prs.next()) {
				// String turnover = prs.getString("turnover");
				// if ("0".equals(turnover)) {
				// yesP = true;
				// }
				// if ("1".equals(turnover)) {
				// todP = true;
				// }
				// }
				// prs.close();
				//
				// String cyear = datetime.toString("YYYY-MM");
				//
				// int pkid = 0;
				// if ((yesP == false) || (todP == false)) {
				// String idsql = "select max(check_id) max from jan_attence";
				// ResultSet rs3 = st.executeQuery(idsql);
				// String id = "";
				// while (rs3.next()) {
				// id = rs3.getString("max");
				// }
				// rs3.close();
				// System.out.println("id:" + id);
				// pkid = Integer.parseInt(id);
				// pkid++;
				// }
				//
				// if ((yesP == false) && (todP == false)) {
				// String cday = datetime.toString("dd");
				// String cmday = datetime.minusDays(1).toString("dd");
				//
				// System.out.println(cyear + " : " + cday);
				// String s1 = "day" + cmday;
				// String s2 = "day" + cday;
				//
				// StringBuffer qry = new StringBuffer("select
				// ").append(s1).append(",").append(s2)
				// .append(" from jan_duty where
				// cyear='").append(cyear).append("' and dept='公安分局'");
				// System.out.println(qry.toString());
				// ResultSet rsq = st.executeQuery(qry.toString());
				// String st1 = null;
				// String st2 = null;
				// while (rsq.next()) {
				// st1 = rsq.getString(s1);
				// st2 = rsq.getString(s2);
				// }
				// rsq.close();
				// System.out.println(st1 + " , " + st2);
				//
				// String id_qry = "select name,identity_id from jan_person_cast
				// where dept_name='公安分局' and name='"
				// + st1 + "' or name='" + st2 + "'";
				// ResultSet rss = st.executeQuery(id_qry);
				// while (rss.next()) {
				// String cname = rss.getString("name");
				// String cid = rss.getString("identity_id");
				//
				// int res = 0;
				// pkid++;
				// if (cname.equals(st1)) {
				// StringBuffer isb = new StringBuffer("insert into jan_attence
				// values('").append(cname)
				// .append("','").append(nowDate).append("','','").append("公安分局")
				// .append("','08:24:00',false,'").append(pkid).append("','").append(cid)
				// .append("','3')");
				// System.out.println(isb.toString());
				// res = st.executeUpdate(isb.toString());
				// } else if (cname.equals(st2)) {
				// StringBuffer isb = new StringBuffer("insert into jan_attence
				// values('").append(cname)
				// .append("','").append(nowDate).append("','','").append("公安分局")
				// .append("','08:24:00',false,'").append(pkid).append("','").append(cid)
				// .append("','4')");
				// System.out.println(isb.toString());
				// res = st.executeUpdate(isb.toString());
				// }
				//
				// }
				// rss.close();
				// } else if (yesP == false) {
				// String cmday = datetime.minusDays(1).toString("dd");
				//
				// } else if (todP == false) {
				// String cday = datetime.toString("dd");
				//
				// }
				// }

				StringBuffer sb = new StringBuffer();
				sb.append(
						"select a.person_name,a.dept_name,a.event_card,a.event_time,b.phone from ac_event a,oc_person b ");
				sb.append(" where a.event_card=b.identity_id ");
				sb.append(" and a.receive_time>'").append(timestap).append("'");
				// 本地时间戳与服务器有差异,最好做到时统

				ResultSet rs = st.executeQuery(sb.toString());
				int count = 0;
				while (rs.next()) {
					Map<String, String> map = new HashMap<String, String>();
					map.put("name", rs.getString("person_name"));
					map.put("dept", rs.getString("dept_name"));
					map.put("phone", rs.getString("phone"));
					map.put("id", rs.getString("event_card"));
					map.put("checktime", rs.getString("event_time"));
					list.add(map);
					count++;
				}
				rs.close();

				// new record, insert or update
				if (count != 0) {
					
					Iterator<Map<String, String>> it = list.iterator();
					while (it.hasNext()) {
						System.out.println();
						System.out.println("---------");

						Map<String, String> map = it.next();

						// 判断当日该部门是否已经签到，若签到过则更新，否则插入
						// turnover字段0签退，1签到
						// 如果昨天的1:放到今天的0
						// 如果没有昨天:放今天的1
						// 每次先判断有无替班人员 只需判断中途换班，其他情况不影响

						StringBuffer tmp = new StringBuffer();
						tmp.append("select * from jan_attence where check_date='").append(yestap)
								.append("' and person_name='").append(map.get("name")).append("'");
						System.out.println(tmp.toString());
						ResultSet yesQueryRs = st.executeQuery(tmp.toString());

						boolean hasAttence = false;
						while (yesQueryRs.next()) {
							int turnover = yesQueryRs.getInt("turnover");
							if (turnover == 1) {
								hasAttence = true;
								break;
							}
						}
						yesQueryRs.close();

						// turnover : 0
						if (hasAttence) {
							System.out.println("昨天值过班");

							/*
							 * 昨天值过班，今天15秒内连刷两次判定为同时交接班，连值，trunover=1，且此记录可以被覆盖
							 */
							String eventtime = map.get("checktime");
							DateTimeFormatter format = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss");
							DateTime dt = DateTime.parse(eventtime, format);
							String secAgo = dt.minusSeconds(16).toString("YYYY-MM-dd HH:mm:ss");

							StringBuffer isCon = new StringBuffer("select * from ac_event where event_time>='")
									.append(secAgo).append("' and event_time<'").append(eventtime)
									.append("' and event_card='").append(map.get("id")).append("'");
							System.out.println(isCon);

							ResultSet crs = st.executeQuery(isCon.toString());
							int ccount = 0;
							while (crs.next()) {
								ccount++;
							}
							crs.close();
							System.out.println("ccount:" + ccount);

							if (ccount == 0) {
								System.out.println("非连值情况");

								// cut here
								StringBuffer sql = new StringBuffer();
								sql.append("select * from jan_attence where check_date='").append(nowDate).append("'")
										.append(" and person_dept='").append(map.get("dept"))
										.append("' and turnover=0");

								ResultSet rs2 = st.executeQuery(sql.toString());
								String check_id = "";
								while (rs2.next()) {
									// primary key
									check_id = rs2.getString("check_id");
								}
								rs2.close();

								String update = "";
								if (!"".equals(check_id)) {// not null ,update
									update = "update jan_attence set person_name='" + map.get("name")
											+ "',person_phone='" + map.get("phone") + "',check_time='"
											+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
											+ "', is_valid=false where check_id='" + check_id + "'";
									st.execute(update);
									// boolean res = st.execute(update);
								} else {// null, insert with new check_id
									String idsql = "select max(check_id) max from jan_attence";
									ResultSet rs3 = st.executeQuery(idsql);
									String id = "";
									while (rs3.next()) {
										id = rs3.getString("max");
									}
									System.out.println("id:" + id);
									int pkid = Integer.parseInt(id);
									pkid++;
									rs3.close();

									update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
											+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
											+ map.get("dept") + "','" + map.get("checktime").substring(11) + "',true,'"
											+ pkid + "','" + map.get("id") + "',0)";
									System.out.println(update);
									boolean res = st.execute(update);
									System.out.println("insert:" + res);
								}

								// end cut
							} else {
								System.out.println("可能为连值");
								/* 判断今天是否已有接班人员，如有不做任何操作，否则填到接班人员 */
								StringBuffer sb3 = new StringBuffer();
								sb3.append("select * from jan_attence where check_date='").append(nowDate).append("'")
										.append(" and person_dept='").append(map.get("dept"))
										.append("' and turnover=1");
								System.out.println(sb3);
								ResultSet rs3 = st.executeQuery(sb3.toString());
								int count3 = 0;
								while (rs3.next()) {
									count3++;
								}
								System.out.println("count3:" + count3);
								rs3.close();

								if (count3 == 0) {
									// 今天还没接班的
									System.out.println("今天还没接班的");
									String idsql = "select max(check_id) max from jan_attence";
									ResultSet rs33 = st.executeQuery(idsql);
									String id = "";
									while (rs33.next()) {
										id = rs33.getString("max");
									}
									System.out.println("id:" + id);
									int pkid = Integer.parseInt(id);
									pkid++;
									rs33.close();

									String isb = "";
									isb = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
											+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
											+ map.get("dept") + "','" + map.get("checktime").substring(11) + "',true,'"
											+ pkid + "','" + map.get("id") + "',1)";
									System.out.println(isb);
									boolean res = st.execute(isb);
									System.out.println("insert:" + res);
								} else {
									// 今天已经有接班的了，什么都不做
									System.out.println("今天已经有接班的了，什么都不做");
								}
							}
							/*
							 * end for continue duty 2017-08-21
							 */

							// end for hasAttence
						} else { // turnover : 1
							System.out.println("昨天没值过班");

							// 需要考虑中途换班情况
							/*
							 * modify 2017.09.26 SMS 和 9527 存在身份证号码不统一情况
							 * 之前情况一天一个部门只存在1条记录，现在存在多条，需要修改
							 */
							String dept = map.get("dept");

							StringBuffer agentSql = new StringBuffer();

							// agentSql.append("select a.agent_department_name
							// dept,a.attendance_time dt,a.agent_id_card,")
							// // .append("(select name agent_name from
							// // oc_person where
							// // identity_id=a.agent_id_card),")
							// .append("(select name agent_name from
							// jan_person_cast where identity_id=a.agent_id_card
							// or indentity_id_sms=a.agent_id_card),")
							// .append("a.create_id_card,")
							// // .append("(select name create_name from
							// // oc_person where
							// // identity_id=a.create_id_card)")
							// .append("(select name create_name from
							// jan_person_cast where
							// identity_id=a.create_id_card or
							// indentity_id_sms=a.create_id_card) ")
							// .append("from tm_sms_attendance_agent
							// a").append(" where a.attendance_time = '")
							// .append(yesDateWithLine).append(" 00:00:00'")
							// .append(" and a.gmt_modified>a.attendance_time ")
							// .append("and
							// a.agent_department_name='").append(dept).append("'");
							// SMS与9257部门不同

							agentSql.append("select a.agent_department_name dept,a.attendance_time dt,a.agent_id_card,")
									.append("(select name agent_name from jan_person_cast where identity_id=a.agent_id_card or indentity_id_sms=a.agent_id_card),")
									.append("a.create_id_card,")
									.append("(select name create_name from jan_person_cast where identity_id=a.create_id_card or indentity_id_sms=a.create_id_card) ")
									.append("from tm_sms_attendance_agent a LEFT JOIN jan_person_cast  b ")
									.append("on a.agent_id_card =b.identity_id  or a.agent_id_card=b.indentity_id_sms ")
									.append("where attendance_time ='").append(yesDateWithLine).append(" 00:00:00'")
									.append(" and a.gmt_modified>a.attendance_time ").append(" and (a.agent_id_card='")
									.append(map.get("id"))
									.append("' or a.agent_id_card=(select indentity_id_sms from jan_person_cast where identity_id='")
									.append(map.get("id")).append("'))");

							// System.out.println(agentSql);

							ResultSet agentSt = st.executeQuery(agentSql.toString());
							int agCount = 0;
							String agTime = "";
							String agentIdCard = "";
							String agentName = "";
							String createIdCart = "";
							String creatrName = "";
							while (agentSt.next()) {
								agTime = agentSt.getString("dt");
								agentIdCard = agentSt.getString("agent_id_card");
								agentName = agentSt.getString("agent_name");
								createIdCart = agentSt.getString("create_id_card");
								creatrName = agentSt.getString("create_name");
								agCount++;
							}
							agentSt.close();

							// System.out.println("agentCount:"+agCount);
							// 换班申请加上验证人员 是否当天的值班人员，不是不予处理
							// create_id_card == 昨天1
							// modify by YX 2017-11-13
							if (agCount > 0) {
								String yes = datetime.minusDays(1).toString("YYYYMMdd");
								StringBuffer check_sb = new StringBuffer("select * from jan_attence where check_date='")
										.append(yes).append("' and person_dept='").append(dept)
										.append("' and turnover=1 and person_id='").append(createIdCart).append("'");
								ResultSet checkSt = st.executeQuery(check_sb.toString());
								int tcount = 0;
								while (checkSt.next()) {
									tcount++;
								}

								if (tcount == 0) {
									agCount = 0;
								}

								checkSt.close();
							}
							// end modify

							// 无替换班
							if (agCount == 0) {
								System.out.println("there is no aggent record");

								System.out.println("这人是来接班的");

								StringBuffer sql = new StringBuffer();
								sql.append("select * from jan_attence where check_date='").append(nowDate).append("'")
										.append(" and person_dept='").append(dept).append("' and turnover=1");

								ResultSet rs2 = st.executeQuery(sql.toString());
								String check_id = "";
								while (rs2.next()) {
									// primary key
									check_id = rs2.getString("check_id");
								}
								rs2.close();

								String update = "";
								if (!"".equals(check_id)) {// not null
															// ,update
									update = "update jan_attence set person_name='" + map.get("name")
											+ "',person_phone='" + map.get("phone") + "',check_time='"
											+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
											+ "', is_valid=false where check_id='" + check_id + "'";
									st.execute(update);
									// boolean res = st.execute(update);
								} else {// null, insert with new check_id
									String idsql = "select max(check_id) max from jan_attence";
									ResultSet rs3 = st.executeQuery(idsql);
									String id = "";
									while (rs3.next()) {
										id = rs3.getString("max");
									}
									System.out.println("id:" + id);
									int pkid = Integer.parseInt(id);
									pkid++;
									rs3.close();

									update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
											+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
											+ map.get("dept") + "','" + map.get("checktime").substring(11) + "',true,'"
											+ pkid + "','" + map.get("id") + "',1)";
									System.out.println(update);
									boolean res = st.execute(update);
									System.out.println("insert:" + res);
								}

							} else {
								System.out.println(agTime + " " + agentIdCard + " " + agentName + " " + createIdCart
										+ "" + creatrName);
								if (agentName.equals(map.get("name"))) {
									System.out.println("这人是来交班的");

									StringBuffer sql = new StringBuffer();
									sql.append("select * from jan_attence where check_date='").append(nowDate)
											.append("'").append(" and person_dept='").append(map.get("dept"))
											.append("' and turnover=0");

									ResultSet rs2 = st.executeQuery(sql.toString());
									String check_id = "";
									while (rs2.next()) {
										// primary key
										check_id = rs2.getString("check_id");
									}
									rs2.close();

									String update = "";
									if (!"".equals(check_id)) {// not null
																// ,update
										update = "update jan_attence set person_name='" + map.get("name")
												+ "',person_phone='" + map.get("phone") + "',check_time='"
												+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
												+ "', is_valid=false where check_id='" + check_id + "'";
										st.execute(update);
										// boolean res = st.execute(update);
									} else {// null, insert with new check_id
										String idsql = "select max(check_id) max from jan_attence";
										ResultSet rs3 = st.executeQuery(idsql);
										String id = "";
										while (rs3.next()) {
											id = rs3.getString("max");
										}
										System.out.println("id:" + id);
										int pkid = Integer.parseInt(id);
										pkid++;
										rs3.close();

										update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
												+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
												+ map.get("dept") + "','" + map.get("checktime").substring(11)
												+ "',true,'" + pkid + "','" + map.get("id") + "',0)";
										System.out.println(update);
										boolean res = st.execute(update);
										System.out.println("insert:" + res);
									}

								} else {
									System.out.println("这人是来接班的");

									StringBuffer sql = new StringBuffer();
									sql.append("select * from jan_attence where check_date='").append(nowDate)
											.append("'").append(" and person_dept='").append(dept)
											.append("' and turnover=1");

									ResultSet rs2 = st.executeQuery(sql.toString());
									String check_id = "";
									while (rs2.next()) {
										// primary key
										check_id = rs2.getString("check_id");
									}
									rs2.close();

									String update = "";
									if (!"".equals(check_id)) {// not null
																// ,update
										update = "update jan_attence set person_name='" + map.get("name")
												+ "',person_phone='" + map.get("phone") + "',check_time='"
												+ map.get("checktime").substring(11) + "',person_id='" + map.get("id")
												+ "', is_valid=false where check_id='" + check_id + "'";
										st.execute(update);
										// boolean res = st.execute(update);
									} else {// null, insert with new check_id
										String idsql = "select max(check_id) max from jan_attence";
										ResultSet rs3 = st.executeQuery(idsql);
										String id = "";
										while (rs3.next()) {
											id = rs3.getString("max");
										}
										System.out.println("id:" + id);
										int pkid = Integer.parseInt(id);
										pkid++;
										rs3.close();

										update = "insert into jan_attence (person_name,check_date,person_phone,person_dept,check_time,is_valid,check_id,person_id,turnover) values('"
												+ map.get("name") + "','" + nowDate + "','" + map.get("phone") + "','"
												+ map.get("dept") + "','" + map.get("checktime").substring(11)
												+ "',true,'" + pkid + "','" + map.get("id") + "',1)";
										System.out.println(update);
										boolean res = st.execute(update);
										System.out.println("insert:" + res);
									}

								}
							}

						}

					}
				}

				//modify @ 2017-12-2
				//update all to true 
				//String updateall="UPDATE jan_attence set is_valid=TRUE where check_date='"+nowDate+"'";
				//st.executeUpdate(updateall);				
				//end modify @2017-12-2
				
				st.close();
				conn.close();

//				Thread.sleep(5000);
				Thread.sleep(4000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}
